package tt.dz.egobus.servicemanager.cycle;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import tt.dz.egobus.servicemanager.core.exception.BusinessException;

import javax.sql.DataSource;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Optional;

/**
 * @author Anson
 * @date 17-5-12
 * @name 初始化db相关操作
 * 初始化数据库脚本文本定义规则:  索引排序号(2位) + db + 执行类别 + .sql
 * @since 1.0.4
 */
public class AppDbInitCycleProcessor implements AppCycleProcessor {

    private static final Logger LOGGER = LoggerFactory.getLogger(AppDbInitCycleProcessor.class);

    private static final String DB_DIR = "db";

    private static final String VALIDATE_SQL = DB_DIR + "/90-db-validate.sql";

    private boolean isProcessor = false;

    private Connection connection;

    public AppDbInitCycleProcessor(Connection connection) {
        this.connection = connection;
    }

    public AppDbInitCycleProcessor(DataSource dataSource) {
        if (dataSource != null) {
            try {
                this.connection = dataSource.getConnection();
            } catch (SQLException e) {
            }
        } else {
            this.connection = null;
        }

    }

    @Override
    public boolean isProcessor() {
        return isProcessor;
    }

    @Override
    public boolean processorInit() throws BusinessException {
        if (connection == null) {
            LOGGER.error("当前数据库链接为空");
            return false;
        }

        boolean isExec = this.isNeedExec();
        LOGGER.info("判断当前数据库是否需要初始化isExec={}", isExec);
        if (isExec) {
            File dir = new File(AppDbInitCycleProcessor.class.getResource("/" + DB_DIR).getFile());
            if (dir != null && dir.isDirectory()) {
                //过滤非法定义的sql文件
                File[] files = dir.listFiles(new FilenameFilter() {
                    @Override
                    public boolean accept(File dir, String name) {
                        return name.endsWith(".sql");
                    }
                });

                if (files != null && files.length > 0) {
                    // 排序执行 按照索引号进行排序
                    LOGGER.info("按照索引号进行排序,files={}", files.length);
                    Arrays.sort(files, new Comparator<File>() {
                        @Override
                        public int compare(File o1, File o2) {
                            if (o1 == null) {
                                return -1;
                            }
                            if (o2 == null) {
                                return 1;
                            }
                            String fileName1 = o1.getName();
                            String fileName2 = o2.getName();
                            return fileName1.compareTo(fileName2);
                        }
                    });

                    //执行每一个脚本sql
                    String sql = "";
                    InputStream in = null;
                    String fileName = "";
                    boolean isExecSuccess = false;
                    for (File file : files) {
                        fileName = file.getName();
                        LOGGER.info("执行初始化脚本,file={}", fileName);
                        try {
                            in = new FileInputStream(file);
                            sql = this.builderSql(in);
                            isExecSuccess = this.execSql(sql);
                            LOGGER.info("执行初始化脚本完毕,file={}", fileName);
                        } catch (FileNotFoundException e) {
                            LOGGER.error("执行初始化脚本未找到,file={}", fileName);
                        } catch (IOException e) {
                            LOGGER.error("执行初始化脚本转换inputstream异常,file={}", fileName);
                        } catch (SQLException e) {
                            LOGGER.error("执行初始化脚本执行sql异常,file={}", fileName);

                        } finally {
                            try {
                                in.close();
                            } catch (IOException e) {
                                LOGGER.error("执行初始化脚本关闭inputstream异常,file={}", fileName);
                            }
                        }
                    }
                }
            }
        }
        isProcessor = true;
        return isProcessor;
    }

    @Override
    public boolean processorDestroyed() throws BusinessException {
        try {
            if (connection != null && !this.connection.isClosed()) {
                this.connection.close();
            }
        } catch (SQLException e) {
            LOGGER.error("执行初始化脚本关闭connection异常", e);
        }
        return true;
    }

    /**
     * 判断是否需要执行初始化脚本
     *
     * @return
     */
    private boolean isNeedExec() {
        String validateFile = VALIDATE_SQL;
        LOGGER.info("判断当前数据库是否需要初始化查询的sql文件,validate_sql={}", validateFile);
        InputStream inputStream = null;
        PreparedStatement preparedStatement = null;
        boolean isExec = false;
        try {
            inputStream = AppDbInitCycleProcessor.class.getResourceAsStream("/" + validateFile);
            if (inputStream == null || inputStream.available() == 0) {
                return false;
            }

            String sql = this.builderSql(inputStream);
            preparedStatement = connection.prepareStatement(sql);
            ResultSet rs = preparedStatement.executeQuery();
            if (rs == null) {
                return false;
            }
            int count = Integer.MAX_VALUE;
            while (rs.next()) {
                count = rs.getInt(1);
            }
            return isExec = !(count > 0);
        } catch (IOException e) {
            LOGGER.error("获取校验是否执行初始化数据库数据,读取文件异常", e);
        } catch (SQLException e) {
            LOGGER.error("获取校验是否执行初始化数据库数据,SQL异常", e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
                if (!isExec) {
                    if (!connection.isClosed()) {
                        connection.close();
                    }
                }
            } catch (Exception e) {
            }
        }

        return false;
    }

    /**
     * 转换流为sql语句
     *
     * @param inputStream
     * @return
     * @throws IOException
     */
    private String builderSql(InputStream inputStream) throws IOException {
        if (inputStream == null || inputStream.available() == 0) {
            return "";
        }
        BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(inputStream, "utf-8"));
        StringBuilder stringBuilder = new StringBuilder();
        String line = "";
        while ((line = bufferedReader.readLine()) != null) {
            stringBuilder.append(line + "\n");
        }
        return stringBuilder.toString();
    }

    /**
     * 执行sql语句
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    private boolean execSql(String sql) throws SQLException {
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        return preparedStatement.execute();
    }


}
